/*
Missing Index Details from SQLQuery2.sql - 146.40.63.126.ACRA_DB_LIVE_14Dec2014 (tdadmin (61))
The Query Processor estimates that implementing the following index could improve the query cost by 94.7484%.
*/
--Update CP
--SET CP.BNInfo2 = (Select Max(ContactHistoryID) from tblContactHistory Where ContactProfileID = CP.ContactProfileID And SourceID <> 100)
--FROM tblContactProfile CP

--Update CP SET BNInfo3 = T.CaseProfileIndex
--FROM tblContactProfile CP INNER JOIN
--(	Select Max(CaseProfileIndex) CaseProfileIndex, ContactProfileID from tblCaseProfileIndex CPI
--	WHERE CPI.IsReplyCase = 0
--	Group By ContactProfileID) AS T ON CP.ContactProfileID = T.ContactProfileID

--Update CP SET CP.BNInfo3 = CPI.CaseProfileIndex From tblContactProfile  CP
--INNER JOIN tblTask T ON T.ContactProfileID = CP.ContactProfileID 
--INNER JOIN tblCaseProfileIndex CPI ON T.CaseProfileIndex = CPI.CaseProfileIndex
--Where CP.BNInfo3 IS NULL And T.[Status] <> 100

USE [ACRA_DB_LIVE]
GO
CREATE NONCLUSTERED INDEX [tblContactProfile_NonClusterIndex_EntityID_27Mar2015_12PM]
ON [dbo].[tblContactProfile] ([EntityID])
INCLUDE ([ContactProfileID],[Salutation],[SurName],[Name],[Email],[Mobile],[Office],[Residential])
GO
CREATE NONCLUSTERED INDEX [tblCaseProfileIndex_NonClusterIndex_CaseSourceID_27Mar2015_12PM]
ON [dbo].[tblCaseProfileIndex] ([CaseSourceID])
INCLUDE ([CaseRefID],[CaseStatus],[ProcessedBy],[EscalatedTo],[CreatedDate])
GO
CREATE NONCLUSTERED INDEX [tblCaseProfileIndex_NonCluster_CaseSourceID_Contact_27Mar2015_12PM]
ON [dbo].[tblCaseProfileIndex] ([CaseSourceID])
INCLUDE ([CaseRefID],[EntityID],[ContactProfileID],[CaseStatus],[ProcessedBy],[EscalatedTo],[CreatedDate])
GO
CREATE NONCLUSTERED INDEX [[tblCaseProfileIndex_NonCluster_KeyIndex_27Mar2015_12PM]
ON [dbo].[tblCaseProfileIndex] ([CaseSourceID],[EntityID],[ContactProfileID])
INCLUDE ([CaseRefID],[CaseStatus],[ProcessedBy],[EscalatedTo],[CreatedDate])
GO
CREATE NONCLUSTERED INDEX [tblContactHistory_NonCluster_27Mar2015__12PM]
ON [dbo].[tblContactHistory] ([CaseTypeID])
INCLUDE ([CaseRefNo],[EntityID],[ContactProfileID],[SourceID],[PhoneID],[QueueNo],[CreatedBy])
GO
CREATE NONCLUSTERED INDEX [tblContactProfile_NonClusterIndex_BNInfo2_31Mar2015_1PM]
ON [dbo].[tblContactProfile] ([BNInfo2])
INCLUDE ([ContactProfileID],[EntityID],[Salutation],[SurName],[Name],[Email],[Mobile],[Office],[Residential])
GO
CREATE NONCLUSTERED INDEX [tblContactProfile_NonClusterIndex_BNInfo3_31Mar2015_1_10PM]
ON [dbo].[tblContactProfile] ([BNInfo3])
INCLUDE ([ContactProfileID],[EntityID],[Salutation],[SurName],[Name],[Email],[Mobile],[Office],[Residential])
GO
CREATE NONCLUSTERED INDEX [tblContactProfile_NonClusterIndex_CaseSourceID_CreatedDate_07Apr2015_1_42PM]
ON [dbo].[tblCaseProfileIndex] ([CaseSourceID],[CreatedDate])
INCLUDE ([CaseRefID],[CaseStatus],[ProcessedBy],[EscalatedTo])
GO
CREATE NONCLUSTERED INDEX [tblTask_NonClusterIndex_CaseSourceID_05May2015_12_40PM]
ON [dbo].[tblTask] ([CaseSourceID])
INCLUDE ([CaseRefID],[Status],[ModifiedDate])
GO
CREATE NONCLUSTERED INDEX [tblTask_NonClusterIndex_CaseRefID_CaseSourceID_05May2015_12_40PM]
ON [dbo].[tblTask] ([CaseRefID],[CaseSourceID])
GO
CREATE NONCLUSTERED INDEX [tblCaseProfileIndex_NonClusterIndex_CaseSourceID_05May2015_12_40PM]
ON [dbo].[tblCaseProfileIndex] ([CaseSourceID])
INCLUDE ([CaseProfileIndex],[CaseRefID],[CaseTypeID],[CaseStatus],[ProcessedBy],[ProcessedOn],[ClosedBy],[ClosedOn],[EscalatedTo],[EscalatedToName],[EscalatedToTeamID],[ModifiedDate],[NoOfWorkingDays])
GO
CREATE NONCLUSTERED INDEX [tblContactHistory_NonClusterIndex_IsCallBack_LogBy_05May2015_12_40PM]
ON [dbo].[tblContactHistory] ([IsCallBack],[LogBy])
INCLUDE ([CallBackDateTime])